from sqlalchemy import create_engine,func
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker,relationship
from sqlalchemy import Column, Integer, String, Date,Float, and_
from sqlalchemy.dialects.mysql import DOUBLE

Base = declarative_base()


class Emp(Base):
    __tablename__ = 'emp'
    empno = Column(Integer, primary_key=True)
    ename = Column(String(10))
    job = Column(String(9))
    mgr = Column(Integer)
    hiredate = Column(Date)
    sal = Column(Float)
    comm = Column(Float)
    deptno = Column(Integer)


class Dept(Base):
    __tablename__ = 'dept'
    deptno = Column(Integer, primary_key=True)
    dname = Column(String(14))
    loc = Column(String(13))

class OrmDB:
    def __init__(self, username, password, host, port, dbname):
        sql = 'mysql+pymysql://root:112358@localhost:3306/company?charset=utf8'.format(username, password, host, port, dbname)
        engine = create_engine(sql, max_overflow=5)
        maker = sessionmaker(bind=engine)
        self.session = maker()

    def add_seacher(self, data_list):
        list1 = []
        for item in data_list:
            obj1 = Emp()
            obj1.empno = item[0]
            obj1.ename = item[1]
            obj1.job = item[2]
            obj1.mgr = item[3]
            obj1.hiredate = item[4]
            obj1.sal = item[5]
            obj1.comm = item[6]
            obj1.deptno = item[7]
            list1.append(obj1)
        self.session.add_all(list1)
        self.session.commit()

    def seacher_add(self, data_list):
        list1 = []
        for item in data_list:
            obj1 = Dept()
            obj1.deptno = item[0]
            obj1.dname = item[1]
            obj1.loc = item[2]
            list1.append(obj1)
        self.session.add_all(list1)
        self.session.commit()

    def search_find(self):
        ret = self.session.query(Emp, Dept).filter(Emp.deptno==20,Dept.deptno==20).all()
        # ret = self.session.query(Dept.dname, func.avg(Emp.sal)).group_by(Dept.dname.desc()).all()
        # ret = self.session.query(Emp).join(Dept, Emp.deptno == Dept.deptno, isouter=True).all()
        # ret = self.session.query(Emp).order_by(Emp.sal.asc()).filter(Emp.sal < 3000.0).all()
        return ret
    def search_desc(self):
        ret = self.session.query(func.avg(Emp.sal), Dept.dname).group_by(Dept.dname.desc()).all()
        return ret

    def search_info(self):
        ret = self.session.query(Emp.empno, Emp.ename, Emp.job, Emp.mgr, Dept.loc).join(Dept,Emp.deptno == Dept.deptno, isouter=True).all()
        return ret

    def search_order(self):
        ret = self.session.query(Emp).order_by(Emp.sal.desc(),Emp.hiredate.asc()).filter(and_(Emp.sal<3000, Emp.hiredate>'1981-10-01')).all()
        return ret

if __name__ == '__main__':
    db = OrmDB('root', '123456', 'localhost', 3306, 'mydb')

    data_list = [(7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, None, 20),
                 (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30),
                 (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30),
                 (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, None, 20),
                 (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30),
                 (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, None, 30),
                 (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, None, 10),
                 (7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000, None, 20),
                 (7839, 'KING', 'PRESIDENT', None, '1981-11-17', 5000, None, 10),
                 (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500, 0, 30),
                 (7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100, None, 20),
                 (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, None, 30),
                 (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, None, 20),
                 (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, None, 10)
                 ]
    data_list1 = [
        (10, 'ACCOUNTING', 'NEW YORK'),
        (20, 'RESEARCH', 'DALLAS'),
        (30, 'SALES', 'CHICAGO'),
        (40, 'OPERATIONS', 'BOSTON')
    ]
    # db.add_seacher(data_list) #添加emp数据

    # db.seacher_add(data_list1) #添加dept数据

    # ls = db.search_find()
    # for item in ls:
    #     print(item.Emp.empno,item.Emp.ename,item.Emp.job,item.Emp.mgr,item.Emp.sal,item.Emp.hiredate, item.Dept.dname)

    # row = db.search_desc()
    # print(row)

    # rew3 = db.search_info()
    # for item in rew3:
    # #   print(item)

    # rew4 = db.search_order()
    # for item in rew4:
    #     print(item.empno, item.ename, item.job, item.mgr, item.sal, item.hiredate)
   
